const { execSql } = require('../dbop/mysql')
async function checkUserName(username) {
    const sql = 'select username from users where username=?'
    const result = await execSql(sql, [username])
    return result.length > 0
}
async function addItem(data, tableName = 'articles', isUser = false) {
    let addSql = ''
    let addSqlParams = []

    if (isUser) {
        if (await checkUserName(data.username))
            return null
        addSql = 'INSERT INTO users(username,pwd,age,realName,email,stuNum,className) VALUES(?,?,?,?,?,?,?)'
        addSqlParams = [data.username, data.pwd, data.age, data.realName, data.email, data.stuNum, data.className]
    } else {
        switch (tableName) {
            case 'articles':
                addSql = 'INSERT INTO articles(title,body,userid,completed,categoryid) VALUES(?,?,?,?,?)'
                addSqlParams = [data.title, data.body, data.userid, data.completed, data.categoryid]
                break
            case 'users':
                addSql = 'INSERT INTO users(username,pwd,age,realName,email,stuNum,className) VALUES(?,?,?,?,?,?,?)'
                addSqlParams = [data.username, data.pwd, data.age, data.realName, data.email, data.stuNum, data.className]
                break
            case 'products':
                addSql = 'INSERT INTO products(title,picPaths,userid,price,body,categoryid) VALUES(?,?,?,?,?,?)'
                addSqlParams = [data.title, data.picPaths, data.userid, data.price, data.body, data.categoryid]
                break
            case 'movies':
                addSql = 'INSERT INTO movies(title,picPaths,userid,actors,body,playUrl,categoryid) VALUES(?,?,?,?,?,?,?)'
                addSqlParams = [data.title, data.picPaths, data.userid, data.actors, data.body, data.playUrl, data.categoryid]
                break
            case 'courses':
                addSql = 'INSERT INTO courses(title,picPaths,userid,startTime,body,endTime,categoryid) VALUES(?,?,?,?,?,?,?)'
                addSqlParams = [data.title, data.picPaths, data.userid, data.startTime, data.body, data.endTime, data.categoryid]
                break
            case 'subitems':
                addSql = 'INSERT INTO subitems(title,body,userid,parentid,parentType) VALUES(?,?,?,?,?)'
                addSqlParams = [data.title, data.body, data.userid, data.parentid, data.parentType]
                break
            case 'categories':
                addSql = 'INSERT INTO categories(title,categoryType) VALUES(?,?)'
                if (!data.categoryType) {
                    data.categoryType = '文章'
                }
                addSqlParams = [data.title, data.categoryType]
                break
            case 'comments':
                addSql = 'INSERT INTO comments(body,userid,replyType,articleid) VALUES(?,?,?,?)'
                addSqlParams = [data.body, data.userid, data.replyType, data.articleid]
                break
            default:
                break
        }
    }
    console.log('addSql', addSql)
    console.log('addSqlParams', addSqlParams)
    const result = await execSql(addSql, addSqlParams)
    return result

}

async function updateItem(data, tableName = 'articles', isUser = false) {//只是修改标题和内容
    //??  update where id=data.id
    var updateSql = 'update  ' + tableName + '  set title=?,body=? where id=?'
    var updateSqlParams = [data.title, data.body, data.id]
    //增
    //  connection.query(updateSql,updateSqlParams)
    const result = await execSql(updateSql, updateSqlParams)
    return 'update ok!!!'


}



async function deleteItem(delid, tableName = 'articles') {
    //??  update where id=data.id
    var delSql = 'delete from ' + tableName + ' where id=?'
    var delSqlParams = [delid]
    //增
    //  connection.query(delSql,delSqlParams)
    const result = await execSql(delSql, delSqlParams)
    return 'del ok!!!'
}

async function getList(tableName = 'articles', data = null, isUser = false) {

    // 先去缓存里查找，有的话，直接从缓存读取，没有的话，从数据库读取，再存到缓存以便下次请求时使用
    let sql = 'select * from ' + tableName

    if (isUser) {
        sql = 'select * from users'
    } else if (data && data.categoryid) {//这是获取评论
        sql = 'select * from ' + tableName + ' where categoryid =' + data.categoryid + ' and categoryType = "' + data.categoryType + '"'
    }
    else if (data && data.articleid) {//这是获取评论
        sql = 'select * from ' + tableName + ' where articleid =' + data.articleid + ' and replyType = "' + data.replyType + '"'
    } else if (data && data.parentid) {//这是获取评论
        sql = 'select * from ' + tableName + ' where parentid =' + data.parentid + ' and parentType = "' + data.parentType + '"'
    }

    const result = await execSql(sql)

    const myarray = JSON.parse(JSON.stringify(result))   //RowDataPacket
    return myarray

    //存到缓存，后面从缓存里面获取
}


async function getSingle(data, tableName = 'articles', isLogin = false) {

    // 先去缓存里查找，有的话，直接从缓存读取，没有的话，从数据库读取，再存到缓存以便下次请求时使用
    let sql = 'select * from ' + tableName + ' where id = ?'
    let sqlParams = [data.id]
    console.log('sql', sql)
    console.log('data', data)
    if (isLogin) {
        sql = 'select id,username from users where username=? and pwd=?'
        sqlParams = [data.username, data.pwd]
    }
    const result = await execSql(sql, sqlParams)
    const myarray = JSON.parse(JSON.stringify(result))   //RowDataPacket
    // console.log(myarray)
    if (myarray.length)
        return myarray[0]
    else return null

    //存到缓存，后面从缓存里面获取
}


module.exports = {
    getList, addItem, deleteItem, updateItem, getSingle, checkUserName
}